using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using System.Diagnostics;
using System.Windows.Forms;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Base;
using DevExpress.XtraGrid.Views.Grid;
using System.Linq;

/// Is a simple .NET module to assist 
/// in exchanging data between Excel and .NET  using COM
/// but without using Interop.Excel.dll (900K), Iterop.Office.dll (150K), 
/// Interop.VBIDE.dll (50K).

namespace Nicekit.Excel
{

    #region BaseObject

    /// <summary>
    /// Base class for all COM objects
    /// </summary>
    public class BaseObject : IDisposable
    {
        protected object excelObject = null;

        public BaseObject (object theExcelObject)
        {
            excelObject = theExcelObject;
        }

        public BaseObject ()
        {
        }

        public static void disposeObject (object theObject)
        {
            if(theObject != null)
            {
                Marshal.ReleaseComObject(theObject);
                theObject = null;
            }
        }

        public virtual void Dispose ()
        {
            disposeObject(excelObject);
        }

        public object ExcelObject
        {
            get { return excelObject; }
        }

        public void setProperty (string thePropertyName, object theValue)
        {
            excelObject.GetType().InvokeMember(thePropertyName,
                                               BindingFlags.SetProperty, null, excelObject,
                                               new object[] {theValue});
        }

        public object getProperty (string thePropertyName)
        {
            return excelObject.GetType().InvokeMember(thePropertyName,
                                                      BindingFlags.GetProperty, null, excelObject,
                                                      null);
        }

        public object getProperty (string thePropertyName, object theParam1)
        {
            return excelObject.GetType().InvokeMember(thePropertyName,
                                                      BindingFlags.GetProperty, null, excelObject,
                                                      new object[] {theParam1});
        }

        public object getProperty (string thePropertyName, object theParam1, object theParam2)
        {
            return excelObject.GetType().InvokeMember(thePropertyName,
                                                      BindingFlags.GetProperty, null, excelObject,
                                                      new object[] {theParam1, theParam2});
        }

        public object invokeMethod (string theMethodName)
        {
            return excelObject.GetType().InvokeMember(theMethodName,
                                                      BindingFlags.InvokeMethod, null,
                                                      excelObject,
                                                      null);
        }

        public object invokeMethod (string theMethodName, object theParam1)
        {
            return excelObject.GetType().InvokeMember(theMethodName,
                                                      BindingFlags.InvokeMethod, null,
                                                      excelObject,
                                                      new object[] {theParam1});
        }

    }

    #endregion

    #region Values

    /// <summary>
    /// Direct access to values of cell from Sheet object 
    /// without creating the Range object.
    /// Get and set the value of cells.
    /// Please use from sheet class.
    /// </summary>
    /// <example>
    /// ...
    /// sheet.Values["A5"]="123";
    /// ...
    /// sheet.Values["R5C4"]="123";
    /// ...
    /// s  = Convert.ToString(sheet.Values["A6"]);
    /// ...
    /// s  = Convert.ToString(sheet.Values["R6C8"]);
    /// </example>
    public class Values
    {
        private Sheet sheet = null;

        public Values (Sheet theSheet)
        {
            sheet = theSheet;
        }


        public object this [string theIndex]
        {
            set
            {
                object r = sheet.getRange(theIndex);
                try
                {
                    r.GetType().InvokeMember("Value",
                                             BindingFlags.SetProperty, null, r,
                                             new object[] {value});
                }
                finally
                {
                    Excel.disposeObject(r);
                }
            }
            get
            {
                object r = sheet.getRange(theIndex);
                try
                {
                    return r.GetType().InvokeMember("Value",
                                                    BindingFlags.GetProperty, null, r,
                                                    null);
                }
                finally
                {
                    Excel.disposeObject(r);
                }
            }
        }
    }

    #endregion

    #region Font

    /// <summary>
    /// Font used in the Range class.
    /// </summary>
    /// <remarks>
    /// Object created and disposed in every Range object. 
    /// </remarks>
    /// <example>
    /// using (Range r = sh.Ranges["A5","A10"]){
    ///   r.Font.Bold = true;
    ///   r.Font.Italic = true;
    ///   r.Font.Underline = true;
    ///   r.Font.Size = 24;
    ///   r.Font.Name = "Times New Roman";//"Courier New";
    ///   r.Font.Color = System.Drawing.Color.Red;
    /// } 
    /// </example>
    public class Font : BaseObject
    {

        public Font (object theExcelObject)
            : base(theExcelObject)
        {
        }

        public bool Bold
        {
            set { setProperty("Bold", value); }
            get { return Convert.ToBoolean(getProperty("Bold")); }
        }

        public bool Italic
        {
            set { setProperty("Italic", value); }
            get { return Convert.ToBoolean(getProperty("Italic")); }
        }

        public bool Underline
        {
            set { setProperty("Underline", value); }
            get { return Convert.ToBoolean(getProperty("Underline")); }
        }

        public int Size
        {
            set { setProperty("Size", value); }
            get { return Convert.ToInt32(getProperty("Size")); }
        }

        public string Name
        {
            set { setProperty("Name", value); }
            get { return Convert.ToString(getProperty("Name")); }
        }

        public System.Drawing.Color Color
        {
            set { setProperty("Color", System.Drawing.ColorTranslator.ToOle(value)); }
            get
            {
                return (System.Drawing.ColorTranslator.FromOle(
                                                               Convert.ToInt32(getProperty("Color"))));
            }
        }

    }

    #endregion

    #region Border

    /// <summary>
    /// To get the Border object use the Range object (Ranges).
    /// </summary>
    /// <remarks>
    /// Please dispose this object in program. 
    /// </remarks>
    public class Border : BaseObject
    {

        public Border (object theExcelObject)
            : base(theExcelObject)
        {
        }

        public System.Drawing.Color Color
        {
            set { setProperty("Color", System.Drawing.ColorTranslator.ToOle(value)); }
            get
            {
                return (System.Drawing.ColorTranslator.FromOle(
                                                               Convert.ToInt32(getProperty("Color"))));
            }
        }

        public Excel.xlBorderWeight Weight
        {
            set
            {
                Int32 i = 0;
                unchecked
                {
                    i = (int) value;
                }
                setProperty("Weight", i);
            }
            get { return (Excel.xlBorderWeight) (uint) Convert.ToInt32(getProperty("Weight")); }
        }

        public Excel.xlLineStyle LineStyle
        {
            set
            {
                Int32 i = 0;
                unchecked
                {
                    i = (int) value;
                }
                setProperty("LineStyle", i);
            }
            get { return (Excel.xlLineStyle) (uint) Convert.ToInt32(getProperty("LineStyle")); }
        }

    }

    #endregion

    #region Borders

    /// <summary>
    /// Indexer of Border objects. Not the COM object!
    /// Used for access to Border objects from Range object.
    /// </summary>
    public class Borders
    {
        private Range range = null;

        public Borders (Range theRange)
        {
            range = theRange;
        }

        public Border this [Excel.xlBordersIndex theIndex]
        {
            get { return new Border(range.getBorder(theIndex)); }
        }

    }

    #endregion

    #region Interior

    /// <summary>
    /// Interior used in Range object.
    /// </summary>
    /// <remarks>
    /// Object created and disposed in every Range object. 
    /// </remarks>
    public class Interior : BaseObject
    {

        public Interior (object theExcelObject)
            : base(theExcelObject)
        {
        }


        public System.Drawing.Color Color
        {
            set { setProperty("Color", System.Drawing.ColorTranslator.ToOle(value)); }
            get
            {
                return (System.Drawing.ColorTranslator.FromOle(
                                                               Convert.ToInt32(getProperty("Color"))));
            }
        }

    }

    #endregion

    #region Range

    /// <summary>
    /// Base class for Range objects.
    /// Provide access for properties of cell. 
    /// </summary>
    /// <example>
    /// ...
    /// using (Range r = sheet.Ranges["A5","A10"]){...
    /// ...
    /// using (Range r = sheet.Ranges["R2C10"]){...
    /// ...
    /// using (Range r = sheet.Ranges["A5"]){
    ///   r.Value = "123";
    ///   r.Font.Bold = true;
    ///   r.Font.Size = 24;
    ///   r.Font.Name = "Times New Roman";
    ///   r.Font.Color = System.Drawing.Color.Red;
    /// }
    /// ...
    /// using (Range r = sh.Ranges[String.Format("{0}:{0}", 2)]) {
    ///   r.Font.Bold = true;
    ///   r.HorizontalAlignment = Excel.xlHAlign.xlHAlignCenter;
    /// }
    /// </example>
    public class Range : BaseObject
    {
        private Font font = null;
        private Interior interior = null;
        private Borders borders = null;

        public Range (object theExcelObject)
            : base(theExcelObject)
        {
            font = new Font(getProperty("Font"));
            interior = new Interior(getProperty("Interior"));
            borders = new Borders(this);
        }

        public override void Dispose ()
        {
            if(font != null)
                font.Dispose();
            if(interior != null)
                interior.Dispose();
            base.Dispose();
        }

        /// <summary>
        /// Direct acces to border as COM object
        /// </summary>
        public object getBorder (Excel.xlBordersIndex theIndex)
        {
            Int32 i = 0;
            unchecked
            {
                i = (int) theIndex;
            }
            return getProperty("Borders", i);
        }

        /// <summary>
        /// Frames of cell
        /// </summary>
        /// <example>
        /// using (Border br = r.Borders[ex.xlBordersIndex.xlEdgeTop]) {
        ///   br.Color = System.Drawing.Color.Red; 
        ///   br.Weight = Excel.xlBorderWeight.xlHairline;
        ///   br.LineStyle = Excel.xlLineStyle.xlDouble;
        /// }
        /// </example>
        public Borders Borders
        {
            get { return borders; }
        }

        /// <summary>
        /// Value of cell
        /// </summary>
        /// <example>
        /// using (Range r = sh.Ranges["A5"]) {
        ///   r.Value = textBoxText1.Text;  //"123";
        /// ...
        ///   s  = Convert.ToString(r.Value);
        /// }  
        /// </example>
        public object Value
        {
            set { setProperty("Value", value); }
            get { return getProperty("Value"); }
        }

        /// <summary>
        /// Font of cell
        /// </summary>
        /// <example>
        /// using (Range r = sh.Ranges["A5","A10"]){
        ///   r.Font.Bold = true;
        ///   r.Font.Italic = true;
        ///   r.Font.Underline = true;
        ///   r.Font.Size = 24;
        ///   r.Font.Name = "Times New Roman";//"Courier New";
        ///   r.Font.Color = System.Drawing.Color.Red;
        /// } 
        /// </example>
        public Font Font
        {
            get { return font; }
            set { font = value; }
        }

        /// <summary>
        /// Background color of cell
        /// </summary>
        /// <example>
        ///  using (Range r = sh.Ranges["A5","A10"]){
        ///    r.Interior.Color = System.Drawing.Color.Red;
        ///  }
        /// </example>
        public Interior Interior
        {
            get { return interior; }
            set { interior = value; }
        }

        public Excel.xlHAlign HorizontalAlignment
        {
            set
            {
                Int32 i = 0;
                unchecked
                {
                    i = (int) value;
                }
                setProperty("HorizontalAlignment", i);
            }
            get { return (Excel.xlHAlign) (uint) Convert.ToInt32(getProperty("HorizontalAlignment")); }
        }

        /// <summary>
        /// ColumnWidth color of cell
        /// </summary>
        /// <example>
        /// Ranges["A:A"].ColumnWidth = 10;
        /// (the width value in pixels / 7)
        /// </example> 
        public int ColumnWidth
        {
            set { setProperty("ColumnWidth", value); }
            get { return Convert.ToInt32(getProperty("ColumnWidth")); }
        }

    }

    #endregion

    #region Ranges

    /// <summary>
    /// Indexer of Range objects. Not the COM object!
    /// Used for access to objects Range from Sheet.
    /// </summary>
    public class Ranges
    {
        private Sheet sheet = null;

        public Ranges (Sheet theSheet)
        {
            sheet = theSheet;
        }

        public Range this [string theIndex]
        {
            get { return new Range(sheet.getRange(theIndex)); }
        }

        public Range this [string theIndex, string theIndex2]
        {
            get { return new Range(sheet.getRange(theIndex, theIndex2)); }
        }

    }

    #endregion

    #region Sheet

    /// <summary>
    /// Excel sheet in Sheets list.
    /// </summary>
    /// <example>
    /// Excel ex = new Excel();
    /// ...
    /// Sheet sh = ex.Workbooks[1].ActiveSheet();
    /// ...
    /// Sheet sh = ex.Workbooks[1].Sheets[2];
    /// ...
    /// Sheet sh = ex.Workbooks["123"].Sheets["Test"];
    /// </example>
    public class Sheet : BaseObject
    {
        private Values values = null;
        private Ranges ranges = null;
        private static string csA1 = "ZABCDEFGHIJKLMNOPQRSTUVWXYZ";
        private static int ciA1 = 26;

        #region static string indexFromR1C1ToA1(string theValue)

        public static bool isIndexR1C1 (string theValue)
        {
            if(string.IsNullOrEmpty(theValue))
                return false;
            else
                return (theValue.StartsWith("R") && theValue.Contains("C"));
        }


        public static string columnFromR1C1ToA1 (int theValue)
        {
            string res = "";
            int i = 0;
            while(theValue > 0)
            {
                i = theValue % ciA1;
                res = csA1[i].ToString() + res;
                theValue = (int) (theValue / ciA1);
                if(i == 0)
                    theValue--;
            }
            return res;
        }

        public static string indexFromR1C1ToA1 (string theValue)
        {
            if(isIndexR1C1(theValue))
            {
                int i = theValue.IndexOf("C");
                int x = int.Parse(theValue.Substring(i + 1));
                int y = int.Parse(theValue.Substring(1, i - 1));
                return (columnFromR1C1ToA1(x) + y.ToString());
            }
            return theValue;
        }

        #endregion

        public Sheet (object theExcelObject) : base(theExcelObject)
        {
            values = new Values(this);
            ranges = new Ranges(this);
        }

        /// <summary>
        /// Direct acces to range as COM object
        /// </summary>
        public object getRange (string theIndex)
        {
            return getProperty("Range", indexFromR1C1ToA1(theIndex));
        }

        /// <summary>
        /// Direct acces to range as COM object
        /// </summary>
        public object getRange (string theIndex, string theIndex2)
        {
            return getProperty(
                               "Range",
                               indexFromR1C1ToA1(theIndex),
                               indexFromR1C1ToA1(theIndex2));
        }

        /// <summary>
        /// Directly access to cells value in Sheet
        /// </summary>
        /// <example>
        /// ...
        /// sheet.Values["A5"]="123";
        /// ...
        /// string s  = Convert.ToString(sheet.Values["A5"]);
        /// </example>
        public Values Values
        {
            get { return values; }
        }

        /// <summary>
        /// Range value in Sheet
        /// </summary>
        /// <example>
        /// </example>
        public Ranges Ranges
        {
            get { return ranges; }
        }

    }

    #endregion

    #region Sheets

    /// <summary>
    /// Sheets in workbook
    /// </summary>
    /// <example>
    /// ...
    /// Sheet sh = sheets[1];
    /// ...
    /// Sheet sh = sheets["HOUSE"];
    /// </example>
    public class Sheets : BaseObject
    {

        public Sheets (object theExcelObject)
            : base(theExcelObject)
        {
        }

        public Sheet this [string theIndex]
        {
            get { return new Sheet(getProperty("Item", theIndex)); }
        }

        public Sheet this [int theIndex]
        {
            get { return new Sheet(getProperty("Item", theIndex)); }
        }

        public Sheet Add ()
        {
            return new Sheet(invokeMethod("Add"));
        }


        public int Count ()
        {
            return Convert.ToInt32(getProperty("Count"));
        }

    }

    #endregion

    #region Workbook

    /// <summary>
    /// Workbook in workbooks.
    /// </summary>
    /// <example>
    /// Workbook wb
    /// Sheet sh
    /// ...
    /// sh = wb.ActiveSheet;
    /// ...
    /// sh = wb[1];
    /// ...
    /// sh = wb["WEST"];
    /// </example>
    public class Workbook : BaseObject
    {
        private Sheets sheets = null;

        public Workbook (object theExcelObject) : base(theExcelObject)
        {
            sheets = new Sheets(getProperty("Sheets"));
        }

        public override void Dispose ()
        {
            if(sheets != null)
                sheets.Dispose();
            base.Dispose();
        }

        public void Close (bool theApplyChanges)
        {
            invokeMethod("Close", theApplyChanges);
        }

        public void Save ()
        {
            invokeMethod("Save");
        }

        public void SaveAs (string theFileName)
        {
            invokeMethod("SaveAs", theFileName);
        }

        public Sheets Sheets
        {
            get { return sheets; }
        }

        public Sheet ActiveSheet ()
        {
            return new Sheet(getProperty("ActiveSheet"));
        }

    }

    #endregion

    #region Workbooks

    public class Workbooks : BaseObject
    {

        public Workbooks (object theExcelObject) : base(theExcelObject)
        {
        }

        public Workbook this [string theIndex]
        {
            get { return new Workbook(getProperty("Item", theIndex)); }
        }

        public Workbook this [int theIndex]
        {
            get { return new Workbook(getProperty("Item", theIndex)); }
        }

        public Workbook Add ()
        {
            return new Workbook(invokeMethod("Add"));
        }

        public Workbook AddFromTemplate (string theFileName)
        {
            return new Workbook(invokeMethod("Add", theFileName));
        }


        public Workbook OpenFromFile (string theFileName)
        {
            return new Workbook(invokeMethod("Open", theFileName));
        }

        public int Count ()
        {
            return Convert.ToInt32(getProperty("Count"));
        }
    }

    #endregion

    #region Excel

    /// <summary>
    /// Main class. 
    /// </summary>
    /// <example>
    /// ...
    /// Excel ex = new Excel();
    /// if (ex.Workbooks.Count() == 0)
    ///   ex.Workbooks.Add();
    /// ex.ScreenUpdating = true;
    /// ex.Visible = true;
    /// ...
    /// Sheet sh = ex.Workbooks[1].ActiveSheet();
    /// ...
    /// sh.Values["A5"]="123";
    /// sh.Values["R10C4"]="123";
    /// ...
    /// </example>
    public class Excel : BaseObject
    {
        private static string APP_PROG_ID = "Excel.Application";

        public enum xlWindowState : uint
        {
            xlMaximized = 0xFFFFEFD7U,
            xlMinimized = 0xFFFFEFD4U,
            xlNormal = 0xFFFFEFD1U,
        }

        public enum xlHAlign : uint
        {
            xlHAlignCenter = 0xFFFFEFF4U,
            xlHAlignCenterAcrossSelection = 0x00000007U,
            xlHAlignDistributed = 0xFFFFEFEBU,
            xlHAlignFill = 0x00000005U,
            xlHAlignGeneral = 0x00000001U,
            xlHAlignJustify = 0xFFFFEFDEU,
            xlHAlignLeft = 0xFFFFEFDDU,
            xlHAlignRight = 0xFFFFEFC8U
        }

        public enum xlBordersIndex : uint
        {
            xlDiagonalDown = 0x00000005U,
            xlDiagonalUp = 0x00000006U,
            xlEdgeBottom = 0x00000009U,
            xlEdgeLeft = 0x00000007U,
            xlEdgeRight = 0x0000000AU,
            xlEdgeTop = 0x00000008U,
            xlInsideHorizontal = 0x0000000CU,
            xlInsideVertical = 0x0000000BU
        }

        public enum xlBorderWeight : uint
        {
            xlHairline = 0x00000001U,
            xlMedium = 0xFFFFEFD6U,
            xlThick = 0x00000004U,
            xlThin = 0x00000002U
        }

        public enum xlLineStyle : uint
        {
            xlContinuous = 0x00000001U,
            xlDash = 0xFFFFEFEDU,
            xlDashDot = 0x00000004U,
            xlDashDotDot = 0x00000005U,
            xlDot = 0xFFFFEFEAU,
            xlDouble = 0xFFFFEFE9U,
            xlLineStyleNone = 0xFFFFEFD2U,
            xlSlantDashDot = 0x0000000DU
        }

        public enum xlReferenceStyle : uint
        {
            xlA1 = 0x00000001U,
            xlR1C1 = 0xFFFFEFCAU,
        }

        private Workbooks workbooks = null;

        public Excel () : base()
        {
            try
            {
                excelObject = Marshal.GetActiveObject(APP_PROG_ID);
            }
            catch
            {
                Type t = Type.GetTypeFromProgID(APP_PROG_ID);
                excelObject = Activator.CreateInstance(t);
            }
            workbooks = new Workbooks(getProperty("Workbooks"));
        }

        public override void Dispose ()
        {
            if(workbooks != null)
                workbooks.Dispose();
            base.Dispose();
            GC.GetTotalMemory(true);
        }

        public static void KillAllExcelProcesses ()
        {
            Process[] localByName = Process.GetProcessesByName("excel");
            foreach(Process proc in localByName)
            {
                proc.Kill();
            }
        }

        public bool Visible
        {
            set { setProperty("Visible", value); }
            get { return Convert.ToBoolean(getProperty("Visible")); }
        }

        public bool UserControl
        {
            set { setProperty("UserControl", value); }
            get { return Convert.ToBoolean(getProperty("UserControl")); }
        }

        public bool ScreenUpdating
        {
            set { setProperty("ScreenUpdating", value); }
            get { return Convert.ToBoolean(getProperty("ScreenUpdating")); }
        }

        public bool DisplayAlerts
        {
            set { setProperty("DisplayAlerts", value); }
            get { return Convert.ToBoolean(getProperty("DisplayAlerts")); }
        }

        public xlWindowState WindowState
        {
            set
            {
                Int32 i = 0;
                unchecked
                {
                    i = (int) value;
                }
                setProperty("WindowState", i);
            }
            get { return (xlWindowState) (uint) Convert.ToInt32(getProperty("WindowState")); }
        }

        public xlReferenceStyle ReferenceStyle
        {
            set
            {
                Int32 i = 0;
                unchecked
                {
                    i = (int) value;
                }
                setProperty("ReferenceStyle", i);
            }
            get { return (xlReferenceStyle) (uint) Convert.ToInt32(getProperty("ReferenceStyle")); }
        }

        public Workbooks Workbooks
        {
            get { return workbooks; }
        }

        #region dataGridViewToExcel

        /// <summary>
        /// Convert data from DataGridView to MsExcel
        /// </summary>
        /// <param name="view">grid source</param>
        /// <param name="theStartRow">start row, >=1 </param>
        /// <param name="theStartColumn">start column, >=1</param>
        /// <param name="theColumnsHeaders">if null -> all visible columns from the grid</param>
        /// <param name="theProcessingEvent">if not null -> sender contains the processing index of row</param>
        /// <example>
        /// ...
        /// Excel.dataGridViewToExcel( 1, 1, Grid, null, null);
        /// ...
        /// or
        /// ...
        /// private void toExcelEvent(object sender, EventArgs e)
        /// {
        ///   if (sender is System.Int32) {
        ///     progressBar1.Value = (int)sender;
        ///   }
        /// }
        /// ...
        /// private void toExcel() {
        ///   Excel.dataGridViewToExcel(
        ///     Grid, 
        ///     1, 1,
        ///     null,
        ///     new System.EventHandler(toExcelEvent);
        /// }
        /// ...
        /// </example>
        public static void dataGridViewToExcel (GridView view,
                                                int theStartRow,
                                                int theStartColumn,
                                                ArrayList theColumnsHeaders,
                                                EventHandler theProcessingEvent)
        {
            using(Excel excel = new Excel())
            {
                try
                {
                    //--- open excel
                    if(excel.Workbooks.Count() == 0)
                        excel.Workbooks.Add();
                    //--- check input params 
                    if(theStartRow <= 0)
                        theStartRow = 1;
                    if(theStartColumn <= 0)
                        theStartColumn = 1;
                    //--- start
                    using(Sheet sh = excel.Workbooks[1].Sheets.Add())
                    {
                        int x = theStartColumn;
                        int y = theStartRow;
                        //--- Header
                        foreach(GridColumn col in view.Columns.OrderBy(t=>t.VisibleIndex))
                        {
                            bool b = col.Visible;
                            //if(b && (theColumnsHeaders != null))
                            //    b = theColumnsHeaders.Contains(col.);
                            if (!b)
                            {
                                continue;
                            }
                            string s = Sheet.columnFromR1C1ToA1(x);
                            using(Range r = sh.Ranges[String.Format("{0}:{0}", s)])
                            {
                                r.ColumnWidth = (col.Width / 7);
                            }
                            using(Range r = sh.Ranges[String.Format("R{0}C{1}", y, x)])
                            {
                                //r.Value = col.HeaderText;
                                r.Value = col.GetTextCaption();
                                r.Font.Bold = true;
                                r.HorizontalAlignment = Excel.xlHAlign.xlHAlignCenter;
                            }
                            x++;
                        }
                        y += 2;
                        //--- Data
                        for(int rowIndex = 0; rowIndex < view.RowCount; rowIndex++)
                        {
                            //var dr = view.Get(rowIndex);
                            //foreach (DataGridViewRow dr in view.getro)
                            //{
                            x = theStartColumn;
                            foreach (GridColumn col in view.Columns.OrderBy(t => t.VisibleIndex))
                            {
                                bool b = col.Visible;
                                if (!b)
                                {
                                    continue;
                                }
                                if(theColumnsHeaders != null)
                                {
                                    b = theColumnsHeaders.Contains(col.GetTextCaption());
                                }
                                if (b)
                                {
                                    sh.Values[String.Format("R{0}C{1}", y, x)] = view.GetRowCellValue(rowIndex, col);
                                    x++;
                                }
                            }
                            if(theProcessingEvent != null)
                                theProcessingEvent(rowIndex, null);
                            y++;
                        }
                    }
                }
                finally
                {
                    excel.ScreenUpdating = true;
                    excel.Visible = true;
                }
            }
        }

        #endregion

    }

    #endregion

}
